#include "sqlmanage.h"

SqlManage::SqlManage()
{
    //先判断一下这个默认的连接名是否存在，如果不存在才使用addDatabase()方法，如果存在则使用database()方法
    if(QSqlDatabase::contains("qt_sql_default_connection"))
      db = QSqlDatabase::database("qt_sql_default_connection");
    else
      db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("120.78.130.120");
    db.setDatabaseName("qt_bank_manage_system");
    db.setUserName("root");
    db.setPassword("778855");
    db.setPort(3306);
    bool key=db.open();
    //qDebug() << " error  " << errno << "key : " << key ;

    if(key)
    {
        qDebug()<<"open db ok!";
    }
    else
    {
        qDebug()<<"open db filed!" << errno;
    }

    QSqlError error;
    error=db.lastError();
    if(error.isValid())//发生错误时isValid()返回true
    {
        switch (error.type()) {
        case QSqlError::NoError:
            qDebug()<<"yes";
            break;
        case QSqlError::ConnectionError://连接错语
            qDebug()<<"connect error:"<<error.text();
            break;
        case QSqlError::StatementError://语句错语
            qDebug()<<"syntax error:"<<error.text();
            break;
        case QSqlError::TransactionError://事务错误
            qDebug()<<"transactionError:"<<error.text();
            break;
        default://未知错误
            qDebug()<<"other error:"<<error.text();
            break;
        }
    }
    //创建密码表
    /*QSqlQuery query;
    query.exec("create table password(id varchar(10),pwd varchar(32),primary key(id))");
    query.exec(QString("insert into password values('201871001','123456')"));
    query.exec(QString("insert into password values('201871002','123')"));
    qDebug()<<"create";*/

    //创建商品表
    /*QSqlQuery query;
    query.exec("create table goods(goods_id varchar(10), goods_name varchar(10), category varchar(10), brand varchar(10), size varchar(32), color varchar(10), number int(10), unit varchar(10), into_price float(10), sale_price float(10), sum float(13), primary key(goods_id))");
    query.exec(QString("insert into goods values('2018710001','玉溪','烟', '玉溪', '软盒', '红色', '20', '条', '200', '250', '5000')"));
    qDebug()<<"create";*/

    //创建类别表
    /*QSqlQuery query;
    query.exec("create table category_table(id varchar(10) , brand_name varchar(10), primary key (id))");
    query.exec(QString("insert into category_table values('0','请选择类别')"));
    query.exec(QString("insert into category_table values('01','烟')"));
    query.exec(QString("insert into category_table values('02','饮料')"));*/
}

QSqlQuery SqlManage::queryTable(QString tabeName, QString field, QString keyWord)
{
    QSqlQuery query;
    if(field=="" || keyWord==""){
        query.exec("SELECT * FROM "+tabeName);
    }
    else{
        bool isNum;
        keyWord.toLongLong(&isNum);
        if(isNum){
            query.exec(QString("SELECT * FROM %1 WHERE %2=%3;").arg(tabeName).arg(field).arg(keyWord));
        }
        else{
            query.exec(QString("SELECT * FROM %1 WHERE %2='%3';").arg(tabeName).arg(field).arg(keyWord));
        }
    }
    return query;
}

/*************************************************************
* @projectName   Test
* @brief         SqlManage::querySort
* @param[in]     tableName          要查询的表名
* @param[in]     field              依据的字段
* @param[out]    QSqlQuery          查询的结果集
* @author        liyih
* @date          2018-07-06
**************************************************************/
QSqlQuery SqlManage::querySort(QString tableName, QString field="")
{
    QString command = QString("SELECT * FROM %1;").arg(tableName);
    if(field!=""){
        command = QString("SELECT * FROM %1 ORDER BY %2 ASC;").arg(tableName).arg(field);
    }
    QSqlQuery query;
    query.exec(command);
    return query;
}

QSqlQuery SqlManage::queryGetPage(QString tableName, int page, QString sortField)
{
    if(pageMax == -1){
        this->getTotalPage(tableName);
    }
    if(page > pageMax || page < 1){
        return QSqlQuery(NULL);
    }
    QString command = QString("SELECT * FROM %1 LIMIT %2, %3;")
            .arg(tableName)
            .arg((page-1)*perPageNum)
            .arg(this->perPageNum);
    if(sortField != ""){
        command = QString("SELECT * FROM %1 ORDER BY %2 DESC LIMIT %3, %4;")
                .arg(tableName)
                .arg(sortField)
                .arg((page-1)*perPageNum)
                .arg(this->perPageNum);
    }
    QSqlQuery query;
    query.exec(command);
    return query;
}

int SqlManage::getTotalPage(QString tableName)
{
    if(this->pageMax == -1){
        QSqlQuery countNum;
        countNum.exec("SELECT COUNT(id) FROM " +tableName+ ";");
        countNum.first();
        if((countNum.value("COUNT(id)").toInt() % perPageNum) == 0){
            this->pageMax = countNum.value("COUNT(id)").toInt() / perPageNum;
        }
        else this->pageMax = (countNum.value("COUNT(id)").toInt() / perPageNum) + 1;
    }
    return this->pageMax;
}

bool SqlManage::exec(QString command)
{
    QSqlQuery query;
    if(query.exec(command)){
        return true;
    }
    return false;
}
void SqlManage::closeSql()
{

    db.close();
}

bool SqlManage::addItem(QString command)
{
    QSqlQuery query;
    return query.exec(command);
}

bool SqlManage::delItemById(QString tableName, QString key, long long id)
{
    //TODO
    QString command = QString("DELETE FROM %1 WHERE %2=%3;")
            .arg(tableName)
            .arg(key)
            .arg(id);
    QSqlQuery query;
    return query.exec(command);
}

bool SqlManage::editItemById(QString command)
{
    QSqlQuery query;
    return query.exec(command);
}
